﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DTO;

namespace DAO
{
    public class DAO_TaiKhoan
    {
        //Get Ten Tai Khoan
        public string GetTenTK(int maTK)
        {
            string querySearch = "SELECT HoTen FROM TAIKHOAN where MaTK = " + maTK.ToString();
            DataTable dt = DataProvider.ExecuteQuery(querySearch);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                DataRow dr = dt.Rows[0];
                return dr[0].ToString();
            }
        }
        //TimKiemTaiKhoan để kiểm tra xem user đăng nhập có đúng hay ko
        public DTO_TaiKhoan TimKiemTaiKhoan(string mail, string pass)
        {
            DTO_TaiKhoan acc = new DTO_TaiKhoan();
            string querySearch = "SELECT MaTK, HoTen, Email, Password, DienThoai, LoaiTK, GioiTinh, NgaySinh FROM TAIKHOAN tk where tk.Email = '" +
                           mail + "' AND tk.Password = '" + pass + "'";
            DataTable dt = DataProvider.ExecuteQuery(querySearch);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                DataRow dr = dt.Rows[0];
                acc.MaTK = int.Parse(dr[0].ToString());
                acc.HoTen = dr[1].ToString();
                acc.Email = dr[2].ToString();
                acc.Password = dr[3].ToString();
                acc.DienThoai = dr[4].ToString();
                acc.LoaiTK = int.Parse(dr[5].ToString());
                acc.GioiTinh = Convert.ToBoolean(dr[6].ToString());
                acc.NgaySinh = Convert.ToDateTime(dr[7].ToString());
                return acc;
            }
        }

        //ThemTaiKhoan để cho người dùng đăng kí -- MaTK tự động phát sinh
        //Tra ve true nếu thêm thành công và false nếu việc thêm vào database thất bại
        public bool ThemTaiKhoan(string hoten, string email, string password,
                                 string dienthoai, string loai, string tinhTrang)
        {
            try
            {
                string queryInsert = "INSERT INTO TAIKHOAN VALUES ('" +
                                hoten + "','" +
                                email + "','" +
                                password + "'," +
                                dienthoai + "'," +
                                loai + "'," +
                                tinhTrang + "');";
                DataProvider.ExecuteQuery(queryInsert);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public bool ThemTaiKhoan(string hoten, string email, string password,
                                string dienthoai, string loai, string gioitinh, string ngaysinh, string tinhTrang)
        {
            try
            {
                string queryInsert = "INSERT INTO TAIKHOAN VALUES (N'" +
                                hoten + "','" +
                                email + "','" +
                                password + "', '" +
                                dienthoai + "', '" +
                                loai + "', '" +
                                gioitinh + "', '" +
                                ngaysinh + "', '" +
                                tinhTrang + "');";
                DataProvider.ExecuteQuery(queryInsert);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public DTO_TaiKhoan ThongTinTaiKhoan(string matk)
        {
            DTO_TaiKhoan acc = new DTO_TaiKhoan();
            string querySearch = "SELECT MaTK, HoTen, Email, Password, DienThoai, LoaiTK, GioiTinh, NgaySinh FROM TAIKHOAN WHERE ([MaTK] = '" + matk + "')";
            DataTable dt = DataProvider.ExecuteQuery(querySearch);
            DataRow dr = dt.Rows[0];
            acc.MaTK = int.Parse(dr[0].ToString());
            acc.HoTen = dr[1].ToString();
            acc.Email = dr[2].ToString();
            acc.Password = dr[3].ToString();
            acc.DienThoai = dr[4].ToString();
            acc.LoaiTK = int.Parse(dr[5].ToString());
            acc.GioiTinh = Convert.ToBoolean(dr[6].ToString());
            acc.NgaySinh =Convert.ToDateTime(dr[7].ToString());
            return acc;
        }

        public void Update(DTO_TaiKhoan tk)
        {
            string query = "UPDATE TAIKHOAN SET DienThoai='" + tk.DienThoai + "', HoTen=N'" + tk.HoTen + "', GioiTinh='" + tk.GioiTinh + "',NgaySinh='" + tk.NgaySinh + "', Password='" + tk.Password + "' WHERE ([MaTK] = '" + tk.MaTK + "')";
            DataProvider.ExecuteQuery(query);
        }

        public void SavePassword(string pass, int matk)
        {
            string query = "UPDATE TAIKHOAN SET Password='" + pass + "' WHERE ([MaTK] = '" + matk + "')";
            DataProvider.ExecuteQuery(query);
        }
        public List<DTO_TaiKhoan> Select(int loai)
        {
            List<DTO_TaiKhoan> list = new List<DTO_TaiKhoan>();
            string query = "SELECT HoTen,MaTK,DienThoai,Email,TinhTrang from TAIKHOAN where LoaiTK=" + loai.ToString();
            DataTable TB = DataProvider.ExecuteQuery(query);
            foreach (DataRow dr in TB.Rows)
            {
                DTO_TaiKhoan dto = new DTO_TaiKhoan();
                dto.MaTK = Convert.ToInt32(dr["MaTK"]);
                dto.HoTen = dr["HoTen"].ToString();
                dto.DienThoai = dr["DienThoai"].ToString();
                dto.Email = dr["Email"].ToString();
                dto.TinhTrang = Convert.ToInt32(dr["TinhTrang"]);
                list.Add(dto);
            }

            return list;
        }
        public List<DTO_TaiKhoan> XemChiTiet(int ma)
        {
            List<DTO_TaiKhoan> list = new List<DTO_TaiKhoan>();
            string query = "SELECT HoTen,MaTK,DienThoai,Email,TinhTrang,GioiTinh,NgaySinh,LoaiTK,Password from TAIKHOAN where MaTK=" + ma.ToString();
            DataTable TB = DataProvider.ExecuteQuery(query);
            foreach (DataRow dr in TB.Rows)
            {
                DTO_TaiKhoan dto = new DTO_TaiKhoan();
                dto.MaTK = Convert.ToInt32(dr["MaTK"]);
                dto.HoTen = dr["HoTen"].ToString();
                dto.DienThoai = dr["DienThoai"].ToString();
                dto.Email = dr["Email"].ToString();
                dto.TinhTrang = Convert.ToInt32(dr["TinhTrang"]);
                dto.GioiTinh = Convert.ToBoolean(dr["GioiTinh"]);
                dto.NgaySinh = Convert.ToDateTime(dr["NgaySinh"]);
                dto.LoaiTK = Convert.ToInt32(dr["LoaiTK"]);
                dto.Password = dr["Password"].ToString();
                list.Add(dto);
            }

            return list;
        }
        public bool Delete(int ma)
        {
            string query = "Update TAIKHOAN set TinhTrang=1 where MaTK=" + ma.ToString();
            try
            {
                DataProvider.ExecuteQuery(query);
                return true;
            }
            catch (Exception ee)
            {
                return false;
            }
        }
        public List<DTO_TaiKhoan> Loc(string text, int loai)
        {
            List<DTO_TaiKhoan> list = new List<DTO_TaiKhoan>();
            string query = "SELECT HoTen,MaTK,DienThoai,Email,TinhTrang,GioiTinh,NgaySinh,LoaiTK,Password from TAIKHOAN where HoTen like N'%" + text + "%' And LoaiTK=" + loai.ToString();
            DataTable TB = DataProvider.ExecuteQuery(query);
            foreach (DataRow dr in TB.Rows)
            {
                DTO_TaiKhoan dto = new DTO_TaiKhoan();
                dto.MaTK = Convert.ToInt32(dr["MaTK"]);
                dto.HoTen = dr["HoTen"].ToString();
                dto.DienThoai = dr["DienThoai"].ToString();
                dto.Email = dr["Email"].ToString();
                dto.TinhTrang = Convert.ToInt32(dr["TinhTrang"]);
                dto.GioiTinh = Convert.ToBoolean(dr["GioiTinh"]);
                dto.NgaySinh = Convert.ToDateTime(dr["NgaySinh"]);
                dto.LoaiTK = Convert.ToInt32(dr["LoaiTK"]);
                dto.Password = dr["Password"].ToString();
                list.Add(dto);
            }

            return list;
        }
        public List<DTO_DNKV> XemChiTietBoSung(int ma)
        {
            List<DTO_DNKV> list = new List<DTO_DNKV>();
            string query = "select * from DOANHNGHIEP,KHUVUC where DOANHNGHIEP.TaiKhoan= " + ma.ToString() + "and KHUVUC.MaKV=DOANHNGHIEP.NoiMuonQuangCao";
            DataTable TB = DataProvider.ExecuteQuery(query);
            foreach (DataRow dr in TB.Rows)
            {
                DTO_DNKV dto = new DTO_DNKV();

                dto.MaDN = Convert.ToInt32(dr["MaDN"]);
                dto.TaiKhoan = Convert.ToInt32(dr["TaiKhoan"]);
                dto.ChucVu = dr["ChucVu"].ToString();
                dto.TenDoanhNghiep = dr["TenDoanhNghiep"].ToString();
                //dto.TinhTrang = Convert.ToInt32(dr["TinhTrang"]);
                dto.Website = dr["Website"].ToString();
                dto.DiaChi = dr["DiaChi"].ToString();
                dto.LoaiHinhKinhDoanh = Convert.ToInt32(dr["LoaiHinhKinhDoanh"]);
                dto.TenKhuVuc = dr["TenKhuVuc"].ToString();
                dto.ThongTinHopTac = dr["ThongTinHopTac"].ToString();
                list.Add(dto);
            }

            return list;
        }
        public bool update(string pass, string dt, int GT, int ma,string HoTen,int TinhTrang)
        {

            string query = "Update TAIKHOAN set Password='" + pass + "', DienThoai='" + dt + "', GioiTinh=" + GT.ToString()+",HoTen=N'"+HoTen + "',TinhTrang=" +TinhTrang.ToString()+"Where MaTK=" + ma.ToString();
            try
            {
                DataProvider.ExecuteQuery(query);
                return true;
            }
            catch (Exception ee)
            {
                return false;
            }
        }
        public bool updatebosung(string ChucVu, string TenDN, string web, string DiaChi, string LoaiSP, int NoiQC, string thongtin, int ma)
        {

            string query = "Update DOANHNGHIEP set ChucVu=N'" + ChucVu + "', TenDoanhNghiep=N'" + TenDN + "', Website='" + web + "', DiaChi=N'" + DiaChi + "', LoaiHinhKinhDoanh=N'" + LoaiSP + "', NoiMuonQuangCao=" + NoiQC.ToString() + " , ThongTinHopTac=N'" + thongtin + "' where TaiKhoan=" + ma.ToString();
            try
            {
                DataProvider.ExecuteQuery(query);
                return true;
            }
            catch (Exception ee)
            {
                return false;
            }
        }
        public bool Add(string HoTen, string Email, string Password, string DienThoai, int LoaiTK, int GioiTinh,string NgaySinh)
        {
            string query =" insert into TAIKHOAN Values(N'" + HoTen + "','" + Email + "','" + Password + "','" + DienThoai + "'," + LoaiTK.ToString() + "," + GioiTinh.ToString() + ",'" + NgaySinh + "',0)";
            try
            {
                DataProvider.ExecuteQuery(query);
                return true;
            }
            catch (Exception ee)
            {
                return false;
            }
        }
        public bool Addbosung(string ChucVu, string TenDoanhNghiep, string Website, string DiaChi, int LoaiHinhKinhDoanh, int NoiMuonQuangCao, string ThongTinHopTac, int MaTK)
        {
            string query = "insert into DOANHNGHIEP Values(" + MaTK.ToString() + ",N'" + ChucVu + "',N'" + TenDoanhNghiep + "','" + Website + "',N'" + DiaChi + "'," + LoaiHinhKinhDoanh + "," + NoiMuonQuangCao.ToString() + ",N'" + ThongTinHopTac + "',null)";
            try
            {
                DataProvider.ExecuteQuery(query);
                return true;
            }
            catch (Exception ee)
            {
                return false;
            }
        }
        public bool KTEmail(string t)
        {
            string query = "Select MaTK from TAIKHOAN where Email='" + t + "'";
            List<DTO_TaiKhoan> list = new List<DTO_TaiKhoan>();
            DataTable TB = DataProvider.ExecuteQuery(query);
            foreach (DataRow dr in TB.Rows)
            {
                DTO_TaiKhoan dto = new DTO_TaiKhoan();
                dto.MaTK = Convert.ToInt32(dr["MaTK"]);
                list.Add(dto);
            }
            if (list.Count == 0)
                return true;
            else
                return false;
        }
        public int TimMaTK(string email)
        {
            string query = "select MaTK from TAIKHOAN where Email='" + email + "'";
            List<DTO_TaiKhoan> list = new List<DTO_TaiKhoan>();
            DataTable TB = DataProvider.ExecuteQuery(query);
            foreach (DataRow dr in TB.Rows)
            {
                DTO_TaiKhoan dto = new DTO_TaiKhoan();
                dto.MaTK = Convert.ToInt32(dr["MaTK"]);
                list.Add(dto);
            }
            return list[0].MaTK;
        }
        public List<DTO_DoanhNghiep> DoanhNgiep()
        {
            List<DTO_DoanhNghiep> List = new List<DTO_DoanhNghiep>();
            string query = "select MaDN,TenDoanhNghiep from DOANHNGHIEP ";
            DataTable TB = DataProvider.ExecuteQuery(query);
            foreach (DataRow dr in TB.Rows)
            {
                DTO_DoanhNghiep dto = new DTO_DoanhNghiep();
                dto.MaDN = Convert.ToInt32(dr["MaDN"]);
                dto.TenDoanhNghiep = dr["TenDoanhNghiep"].ToString();
                List.Add(dto);
            }
            return List;
        }
        public List<DTO_KhuVuc> KhuVuc()
        {
            List<DTO_KhuVuc> List = new List<DTO_KhuVuc>();
            string query = "select MaKV,TenKhuVuc from KHUVUC ";
            DataTable TB = DataProvider.ExecuteQuery(query);
            foreach (DataRow dr in TB.Rows)
            {
                DTO_KhuVuc dto = new DTO_KhuVuc();
                dto.MaKV = Convert.ToInt32(dr["MaKV"]);
                dto.TenKhuVuc= dr["TenKhuVuc"].ToString();
                List.Add(dto);
            }
            return List;
        }
        public List<DTO_LoaiKM> LoaiKM()
        {
            List<DTO_LoaiKM> List = new List<DTO_LoaiKM>();
            string query = "select MaLoai,TenLoai from LOAI_KM ";
            DataTable TB = DataProvider.ExecuteQuery(query);
            foreach (DataRow dr in TB.Rows)
            {
                DTO_LoaiKM dto = new DTO_LoaiKM();
                dto.MaLoai = Convert.ToInt32(dr["MaLoai"]);
                dto.TenLoai = dr["TenLoai"].ToString();
                List.Add(dto);
            }
            return List;
        }
    }
}
